package com.ross.web.dao;

import java.util.Calendar;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.AliasToBeanResultTransformer;

import com.ross.web.domain.Product;
import com.ross.web.util.HibernateUtil;

public class ProductDAO extends HibernateUtil {
	
	public List getAllProduct(){
		
		Session session = HibernateUtil.getSession();
		List result = new LinkedList();
		
		try{
			
			result = session.createCriteria(Product.class, "product")
				.setProjection(Projections.projectionList()
						.add(Projections.property("product.prodId"),"prodId")
						.add(Projections.property("product.name"),"name")
						.add(Projections.property("product.price"),"price")
						.add(Projections.property("product.barcode"),"barcode")
						.add(Projections.property("product.information"),"information")
						.add(Projections.property("product.c_id"),"c_id")
						.add(Projections.property("product.category"),"category")
						.add(Projections.property("product.expiryDate"),"expiryDate")
						.add(Projections.property("product.units"),"units")
						.add(Projections.property("product.quantity"),"quantity")
						
						)
						.setResultTransformer(new AliasToBeanResultTransformer(Product.class))
						.list();
			
			
			
		return result;
			
		}finally {
			session.close();
		}		
	}
	
	public List getAllProductOutofStock(){
		
		Session session = HibernateUtil.getSession();
		List result = new LinkedList();
		
		try{
			
			result = session.createCriteria(Product.class, "product")
				.setProjection(Projections.projectionList()
						.add(Projections.property("product.prodId"),"prodId")
						.add(Projections.property("product.name"),"name")
						.add(Projections.property("product.price"),"price")
						.add(Projections.property("product.barcode"),"barcode")
						.add(Projections.property("product.information"),"information")
						.add(Projections.property("product.c_id"),"c_id")
						.add(Projections.property("product.category"),"category")
						.add(Projections.property("product.expiryDate"),"expiryDate")
						.add(Projections.property("product.units"),"units")
						.add(Projections.property("product.quantity"),"quantity")
						).add(Restrictions.lt("product.quantity", 10))
						.setResultTransformer(new AliasToBeanResultTransformer(Product.class))
						.list();
			
			
			
		return result;
			
		}finally {
			session.close();
		}		
	}
	
	public List getAllProductValidated(String barcode){
		
		Session session = HibernateUtil.getSession();
		List result = new LinkedList();
		
		try{
			
			result = session.createCriteria(Product.class, "product")
				.setProjection(Projections.projectionList()
						.add(Projections.property("product.prodId"),"prodId")
						.add(Projections.property("product.name"),"name")
						.add(Projections.property("product.price"),"price")
						.add(Projections.property("product.barcode"),"barcode")
						.add(Projections.property("product.information"),"information")
						.add(Projections.property("product.c_id"),"c_id")
						.add(Projections.property("product.category"),"category")
						.add(Projections.property("product.expiryDate"),"expiryDate")
						.add(Projections.property("product.units"),"units")
						.add(Projections.property("product.quantity"),"quantity")
						).add(Restrictions.and(Restrictions.gt("product.quantity", 0), 
								Restrictions.eq("product.barcode", barcode)))
						.setResultTransformer(new AliasToBeanResultTransformer(Product.class))
						.list();
			
			
			
		return result;
			
		}finally {
			session.close();
		}		
	}
	
	public List getAllProductByExpiry(String barcode){
		
		Session session = HibernateUtil.getSession();
		List result = new LinkedList();
		Calendar cal = Calendar.getInstance();
		Date timeDate = cal.getTime();
		
		try{
			
			result = session.createCriteria(Product.class, "product")
				.setProjection(Projections.projectionList()
						.add(Projections.property("product.prodId"),"prodId")
						.add(Projections.property("product.name"),"name")
						.add(Projections.property("product.price"),"price")
						.add(Projections.property("product.barcode"),"barcode")
						.add(Projections.property("product.information"),"information")
						.add(Projections.property("product.c_id"),"c_id")
						.add(Projections.property("product.category"),"category")
						.add(Projections.property("product.expiryDate"),"expiryDate")
						.add(Projections.property("product.units"),"units")
						.add(Projections.property("product.quantity"),"quantity")
						)
						.add(Restrictions.and(Restrictions.gt("product.expiryDate", timeDate), 
								Restrictions.eq("product.barcode", barcode)))
						.setResultTransformer(new AliasToBeanResultTransformer(Product.class))
						.list();
			
			
			
		return result;
			
		}finally {
			session.close();
		}		
	}
	
	
	
	
	public Product getProductById(int prodId){
		
		Session session = HibernateUtil.getSession();
		List result = new LinkedList();
		
		try{	
			result = session.createCriteria(Product.class, "product")
				.setProjection(Projections.projectionList()
						.add(Projections.property("product.prodId"),"prodId")
						.add(Projections.property("product.name"),"name")
						.add(Projections.property("product.price"),"price")
						.add(Projections.property("product.barcode"),"barcode")
						.add(Projections.property("product.information"),"information")
						.add(Projections.property("product.c_id"),"c_id")
						.add(Projections.property("product.category"),"category")
						.add(Projections.property("product.expiryDate"),"expiryDate")
						.add(Projections.property("product.units"),"units")
						.add(Projections.property("product.quantity"),"quantity")
						)
						.add(Restrictions.eq("product.prodId", prodId))
						.setResultTransformer(new AliasToBeanResultTransformer(Product.class))
						.list();
			
			
			
		return result.isEmpty() ? new Product() : (Product) result.get(0);
			
		}finally {
			session.close();
		}		
	}
	
	public Product getProductByBarCode(String barcode){
		
		Session session = HibernateUtil.getSession();
		List result = new LinkedList();
		
		try{	
			result = session.createCriteria(Product.class, "product")
				.setProjection(Projections.projectionList()
						.add(Projections.property("product.prodId"),"prodId")
						.add(Projections.property("product.name"),"name")
						.add(Projections.property("product.price"),"price")
						.add(Projections.property("product.barcode"),"barcode")
						.add(Projections.property("product.information"),"information")
						.add(Projections.property("product.c_id"),"c_id")
						.add(Projections.property("product.category"),"category")
						.add(Projections.property("product.expiryDate"),"expiryDate")
						.add(Projections.property("product.units"),"units")
						.add(Projections.property("product.quantity"),"quantity")
						)
						.add(Restrictions.eq("product.barcode", barcode))
						.setResultTransformer(new AliasToBeanResultTransformer(Product.class))
						.list();
			
			
			
		return result.isEmpty() ? null : (Product) result.get(0);
			
		}finally {
			session.close();
		}		
	}
	
	public List getProductByName(String query){
		
		Session session = HibernateUtil.getSession();
		List result = new LinkedList();
		
		try{	
			result = session.createCriteria(Product.class, "product")
				.setProjection(Projections.projectionList()
						.add(Projections.property("product.prodId"),"prodId")
						.add(Projections.property("product.name"),"name")
						.add(Projections.property("product.price"),"price")
						.add(Projections.property("product.barcode"),"barcode")
						.add(Projections.property("product.information"),"information")
						.add(Projections.property("product.c_id"),"c_id")
						.add(Projections.property("product.category"),"category")
						.add(Projections.property("product.expiryDate"),"expiryDate")
						.add(Projections.property("product.units"),"units")
						.add(Projections.property("product.quantity"),"quantity")
						)
						.add(Restrictions.like("product.name", "%"+query+"%"))
						.setResultTransformer(new AliasToBeanResultTransformer(Product.class))
						.list();
			
			
			
		return result;
			
		}finally {
			session.close();
		}		
	}
	
	
	public List getProductByKeywords(String prodName, String barcode, Date startExpiryDate, Date endExpiryDate, int category){
		
		Session session = HibernateUtil.getSession();
		List result = new LinkedList();
		Criterion cRestric = null;

		
		if(startExpiryDate!=null && endExpiryDate!=null && category>0){
			System.out.println("eto dapat1!!");
			cRestric = Restrictions.and(Restrictions.like("product.name", "%"+prodName+"%"),
					   Restrictions.and(Restrictions.eq("product.c_id", category), Restrictions.and(Restrictions.like("product.barcode", "%"+barcode+"%"), 
					Restrictions.between("product.expiryDate", startExpiryDate, endExpiryDate))));
		
		}else if((startExpiryDate==null || endExpiryDate==null) && category>0){
			System.out.println("eto dapat2!!");
			cRestric = Restrictions.and(Restrictions.like("product.name", "%"+prodName+"%"),
					   Restrictions.and(Restrictions.eq("product.c_id", category), Restrictions.like("product.barcode", "%"+barcode+"%")));
		
		}else if((startExpiryDate==null || endExpiryDate==null) && category<1){
			System.out.println("eto dapat3!!");
			cRestric = Restrictions.and(Restrictions.like("product.name", "%"+prodName+"%"),Restrictions.like("product.barcode", "%"+barcode+"%"));
		
		}else if((startExpiryDate!=null && endExpiryDate!=null) && category<1){
			System.out.println("eto dapat4!!");
			cRestric = Restrictions.and(Restrictions.like("product.name", "%"+prodName+"%"), Restrictions.and(Restrictions.like("product.barcode", "%"+barcode+"%"), 
					Restrictions.between("product.expiryDate", startExpiryDate, endExpiryDate)));
		}
		
		try{	
			result = session.createCriteria(Product.class, "product")
				.setProjection(Projections.projectionList()
						.add(Projections.property("product.prodId"),"prodId")
						.add(Projections.property("product.name"),"name")
						.add(Projections.property("product.price"),"price")
						.add(Projections.property("product.barcode"),"barcode")
						.add(Projections.property("product.information"),"information")
						.add(Projections.property("product.c_id"),"c_id")
						.add(Projections.property("product.category"),"category")
						.add(Projections.property("product.expiryDate"),"expiryDate")
						.add(Projections.property("product.units"),"units")
						.add(Projections.property("product.quantity"),"quantity")
						)
						.add(cRestric)
						.setResultTransformer(new AliasToBeanResultTransformer(Product.class))
						.list();
			
			
			
		return result;
			
		}finally {
			session.close();
		}		
	}
	
	
	public void insertProduct(Product product){
		Session session = HibernateUtil.getSession();
		Transaction tx = null;
		
		try{
			tx = session.beginTransaction();
			session.save(product);
			tx.commit();
		}catch (RuntimeException e) {
			if (tx != null)
				tx.rollback();
			throw e;
		} finally {
			session.close();
		}
		
	}
	
	public void editProduct(Product product){
		Session session = HibernateUtil.getSession();
		Transaction tx = null;
		
		try{
			tx = session.beginTransaction();
			session.update(product);
			tx.commit();
		}catch (RuntimeException e) {
			if (tx != null)
				tx.rollback();
			throw e;
		} finally {
			session.close();
		}
		
	}
	
	public void deleteProduct(Product product){
		Session session = HibernateUtil.getSession();
		Transaction tx = null;
		
		try{
			tx = session.beginTransaction();
			session.delete(product);
			tx.commit();
		}catch (RuntimeException e) {
			if (tx != null)
				tx.rollback();
			throw e;
		} finally {
			session.close();
		}
		
	}
}